/**
* <p>Title: TestDataHandler.java</p>
* <p>Description: 数据驱动</p>
* <p>Company: www.huoyunren.com</p>
* @author duanxiangyun
* @date 2015年1月7日
* @version 1.0
*/
package com.hxbd.test.handles;

import java.io.File;
import java.io.IOException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.Locale;
import java.util.TimeZone;

import org.testng.Assert;

import jxl.Cell;
import jxl.CellFeatures;
import jxl.CellType;
import jxl.DateCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.CellFormat;
import jxl.read.biff.BiffException;

/**
 * excel数据获取处理类：JXL方式
 */
public class JXLExcelDataHandler{
	// prevent to create instance
	private JXLExcelDataHandler() {
	}

	/**
	 * 初始化对象，加载excel
	 * @return excel对象
	 */
	public static JXLExcelDataHandler getInstance() {
		JXLExcelDataHandler handler = new JXLExcelDataHandler();
		return handler;
	}

	/**
	 * 获得sheet对应数据
	 * @param sheetname 表名
	 * @param filename 文件名
	 * @return 二维数据的数据对象
	 */
	public Object[][] getData(String filename, String sheetname) {
			Workbook workbook;
			Object[][] tempData = null;
			try {
				workbook = Workbook.getWorkbook(new File(filename));			
				if (null == workbook) {
					tempData = null;
				}
				// get the first sheet
				Sheet[] sheets = workbook.getSheets();
				Boolean temp = false;
				for (Sheet tmp : sheets) {
					String sheetName = tmp.getName();
					if (sheetName.equalsIgnoreCase(sheetname))
					{
						tempData = convertSheet2Array(tmp);
						temp = true;
					}
				}
				if (!temp) {
					Assert.fail("Excel找不到对应sheet：" + sheetname);
				}
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				Assert.fail("Excel读取失败,请确认excel文件路径是否正确");
			}
			return tempData;
	}

	/**
	 * Description: convert sheet data.
	 * @param sheet 表名
	 * @return Object[][] 对象数组 ;没有数据返回null
	 */
	private static Object[][] convertSheet2Array(Sheet sheet) {
		int rows = sheet.getRows();
		int cols = sheet.getColumns();
		// locate the last valid row
		for (; rows >= 0; rows--) {
			if (sheet.getCell(0, rows - 1).getContents().trim().length() != 0) {
				break;
			}
		}

		if (rows < 1) {
			return null;
		}

		// locate the last valid column
		for (; cols >= 0; cols--) {
			if (sheet.getCell(cols - 1, 0).getContents().trim().length() != 0) {
				break;
			}
		}
		if (cols < 1) {
			return null;
		}

		// get rid of the first line
		rows--;
		cols--;
		Object[][] datas = new Object[rows][cols];
		// load each record
		// LinkedHashMap<String, Object[][]> data2 = new LinkedHashMap<String,
		// Object[][]>();

		for (int i = 0; i < rows; i++) {
			// String rName = sheet.getCell(0, i).getContents();
			for (int j = 0; j < cols; j++) {
				Cell cell = sheet.getCell(j + 1, i + 1);
				if (cell.getType() == CellType.DATE) { //手动填写模板文件时为 date 类型，其他情况有可能不是date类型
                    DateCell dc = (DateCell) cell;
                    DateFormat a = dc.getDateFormat();
                    SimpleDateFormat sdf2 = (SimpleDateFormat)a;
                    String pattern = sdf2.toPattern();
                    Date date = dc.getDate();
                    long time = (date.getTime() / 1000) - 60 * 60 * 8;
                    date.setTime(time * 1000);
                    SimpleDateFormat sdf = new SimpleDateFormat(pattern.replace("hh", "HH"));
                    String sDate = sdf.format(date);
                    datas[i][j] = sDate;
				}else {
					String value = cell.getContents();
					if (value.equals("Y")) {
						datas[i][j] = Boolean.TRUE;
					} else if (value.equals("N")) {
						datas[i][j] = Boolean.FALSE;
					} else{
						datas[i][j] = value;
					}
				}		
			}
		}
		return datas;
	}
}